package com.ykl.demo.repo;

import com.ykl.demo.entity.SpcRoom;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface SpcRoomRepo extends JpaRepository<SpcRoom, String> {

    @Query(nativeQuery = true, value = "SELECT r.room_id, r.notes, r.address, r.area, r.china_name, r.class_id, r.flag," +
            " r.height, r.hired_rack, r.length, r.maintenance_unit, r.room_type_detail, " +
            " r.start_use_date, r.station_id, r.street, r.to_hire_rack, r.type_id, r.used_area, r.width, r.x, r.y" +
            " FROM ykl.spc_room r left join ykl.spc_station s on r.station_id = s.station_id" +
            " where 1=1 and (?1 = '' or ?1 = r.room_id) and (?2 = '' or ?2 = r.china_name ) and (?3 = '.*' or r.china_name regexp ?3 )" +
            " and (?4 = '.*' or s.china_name regexp ?4 )",
            countQuery = "SELECT count(*) FROM ykl.spc_room r left join ykl.spc_station s on r.station_id = s.station_id" +
            " where 1=1 and (?1 = '' or ?1 = r.room_id) and (?2 = '' or ?2 = r.china_name ) and (?3 = '.*' or r.china_name regexp ?3 )" +
            " and (?4 = '.*' or s.china_name regexp ?4 )")
    Page<SpcRoom> findByRoomIdAndRoomName(String roomId, String roomName, String roomNameLike, String stationNameStart, Pageable page);

    List<SpcRoom> findByStationId(String stationId);
}
